using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using EntLibEC.Info.Finance;

namespace EntLibEC.Dac.Finance
{
	/// <summary>
	/// Summary description for POPayDac.
	/// </summary>
	public class POPayDac
	{
		
		public POPayDac()
		{
		}

		public int InsertMaster(POPayInfo oParam)
		{
			string sql = @"INSERT INTO Ent_Finance_POPay
                            (
                            POSysNo, CurrencySysNo, POAmt, 
                            AlreadyPayAmt, PayStatus, InvoiceStatus, Note
                            )
                            VALUES (
                            @POSysNo, @CurrencySysNo, @POAmt, 
                            @AlreadyPayAmt, @PayStatus, @InvoiceStatus, @Note
                            );set @SysNo = SCOPE_IDENTITY();";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int,4);
			SqlParameter paramCurrencySysNo = new SqlParameter("@CurrencySysNo", SqlDbType.Int,4);
			SqlParameter paramPOAmt = new SqlParameter("@POAmt", SqlDbType.Decimal,9);
			SqlParameter paramAlreadyPayAmt = new SqlParameter("@AlreadyPayAmt", SqlDbType.Decimal,9);
			SqlParameter paramPayStatus = new SqlParameter("@PayStatus", SqlDbType.Int,4);
			SqlParameter paramInvoiceStatus = new SqlParameter("@InvoiceStatus", SqlDbType.Int,4);
			SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,500);

			paramSysNo.Direction = ParameterDirection.Output;

			if ( oParam.POSysNo != AppConst.IntNull)
				paramPOSysNo.Value = oParam.POSysNo;
			else
				paramPOSysNo.Value = System.DBNull.Value;
			if ( oParam.CurrencySysNo != AppConst.IntNull)
				paramCurrencySysNo.Value = oParam.CurrencySysNo;
			else
				paramCurrencySysNo.Value = System.DBNull.Value;
			if ( oParam.POAmt != AppConst.DecimalNull)
				paramPOAmt.Value = oParam.POAmt;
			else
				paramPOAmt.Value = System.DBNull.Value;
			if ( oParam.AlreadyPayAmt != AppConst.DecimalNull)
				paramAlreadyPayAmt.Value = oParam.AlreadyPayAmt;
			else
				paramAlreadyPayAmt.Value = System.DBNull.Value;
			if ( oParam.PayStatus != AppConst.IntNull)
				paramPayStatus.Value = oParam.PayStatus;
			else
				paramPayStatus.Value = System.DBNull.Value;
			if ( oParam.InvoiceStatus != AppConst.IntNull)
				paramInvoiceStatus.Value = oParam.InvoiceStatus;
			else
				paramInvoiceStatus.Value = System.DBNull.Value;
			if ( oParam.Note != AppConst.StringNull)
				paramNote.Value = oParam.Note;
			else
				paramNote.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramPOSysNo);
			cmd.Parameters.Add(paramCurrencySysNo);
			cmd.Parameters.Add(paramPOAmt);
			cmd.Parameters.Add(paramAlreadyPayAmt);
			cmd.Parameters.Add(paramPayStatus);
			cmd.Parameters.Add(paramInvoiceStatus);
			cmd.Parameters.Add(paramNote);

			return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
		}
		public int UpdateMaster(Hashtable paramHash)
		{
			StringBuilder sb = new StringBuilder(200);
			sb.Append("UPDATE Ent_Finance_POPay SET ");

			if ( paramHash != null && paramHash.Count != 0 )
			{
				int index = 0;
				foreach(string key in paramHash.Keys)
				{
					object item = paramHash[key];
					if ( key.ToLower()=="sysno" )
						continue;

					if ( index != 0 )
						sb.Append(",");
					index++;

					
					if (item is int || item is decimal)
					{
						sb.Append(key).Append("=").Append(item.ToString());
					}
					else if (item is string)
					{
						sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
					}
					else if (item is DateTime)
					{
						sb.Append(key).Append(" = cast(").Append( Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
					}
				}
			}

			sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

			return SqlHelper.ExecuteNonQuery(sb.ToString());
		}

		public int DeleteMaster(int poSysNo)
		{
			string sql = "delete from Ent_Finance_POPay where posysno = " + poSysNo;
			SqlCommand cmd = new SqlCommand(sql);
			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int InsertItem(POPayItemInfo oParam)
		{
			string sql = @"INSERT INTO Ent_Finance_POPay_Item
                            (
                            POSysNo, PayStyle, PayAmt, 
                            ReferenceID, CreateTime, CreateUserSysNo,  
                            EstimatePayTime, PayTime, PayUserSysNo, Note, 
                            Status
                            )
                            VALUES (
                            @POSysNo, @PayStyle, @PayAmt, 
                            @ReferenceID, @CreateTime, @CreateUserSysNo,  
                            @EstimatePayTime, @PayTime, @PayUserSysNo, @Note, 
                            @Status
                            );set @SysNo = SCOPE_IDENTITY();";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int,4);
			SqlParameter paramPayStyle = new SqlParameter("@PayStyle", SqlDbType.Int,4);
			SqlParameter paramPayAmt = new SqlParameter("@PayAmt", SqlDbType.Decimal,9);
			SqlParameter paramReferenceID = new SqlParameter("@ReferenceID", SqlDbType.NVarChar,20);
			SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
			SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4);
			SqlParameter paramEstimatePayTime = new SqlParameter("@EstimatePayTime", SqlDbType.DateTime);
			SqlParameter paramPayTime = new SqlParameter("@PayTime", SqlDbType.DateTime);
			SqlParameter paramPayUserSysNo = new SqlParameter("@PayUserSysNo", SqlDbType.Int,4);
			SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramSysNo.Direction = ParameterDirection.Output;

			if ( oParam.POSysNo != AppConst.IntNull)
				paramPOSysNo.Value = oParam.POSysNo;
			else
				paramPOSysNo.Value = System.DBNull.Value;
			if ( oParam.PayStyle != AppConst.IntNull)
				paramPayStyle.Value = oParam.PayStyle;
			else
				paramPayStyle.Value = System.DBNull.Value;
			if ( oParam.PayAmt != AppConst.DecimalNull)
				paramPayAmt.Value = oParam.PayAmt;
			else
				paramPayAmt.Value = System.DBNull.Value;
			if ( oParam.ReferenceID != AppConst.StringNull)
				paramReferenceID.Value = oParam.ReferenceID;
			else
				paramReferenceID.Value = System.DBNull.Value;
			if ( oParam.CreateTime != AppConst.DateTimeNull)
				paramCreateTime.Value = oParam.CreateTime;
			else
				paramCreateTime.Value = System.DBNull.Value;
			if ( oParam.CreateUserSysNo != AppConst.IntNull)
				paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
			else
				paramCreateUserSysNo.Value = System.DBNull.Value;
			if ( oParam.EstimatePayTime != AppConst.DateTimeNull)
				paramEstimatePayTime.Value = oParam.EstimatePayTime;
			else
				paramEstimatePayTime.Value = System.DBNull.Value;
			if ( oParam.PayTime != AppConst.DateTimeNull)
				paramPayTime.Value = oParam.PayTime;
			else
				paramPayTime.Value = System.DBNull.Value;
			if ( oParam.PayUserSysNo != AppConst.IntNull)
				paramPayUserSysNo.Value = oParam.PayUserSysNo;
			else
				paramPayUserSysNo.Value = System.DBNull.Value;
			if ( oParam.Note != AppConst.StringNull)
				paramNote.Value = oParam.Note;
			else
				paramNote.Value = System.DBNull.Value;
			if ( oParam.Status != AppConst.IntNull)
				paramStatus.Value = oParam.Status;
			else
				paramStatus.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramPOSysNo);
			cmd.Parameters.Add(paramPayStyle);
			cmd.Parameters.Add(paramPayAmt);
			cmd.Parameters.Add(paramReferenceID);
			cmd.Parameters.Add(paramCreateTime);
			cmd.Parameters.Add(paramCreateUserSysNo);
			cmd.Parameters.Add(paramEstimatePayTime);
			cmd.Parameters.Add(paramPayTime);
			cmd.Parameters.Add(paramPayUserSysNo);
			cmd.Parameters.Add(paramNote);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
		} 
		public int UpdateItem(Hashtable paramHash)
		{
			StringBuilder sb = new StringBuilder(200);
			sb.Append("UPDATE Ent_Finance_POPay_Item SET ");

			if ( paramHash != null && paramHash.Count != 0 )
			{
				int index = 0;
				foreach(string key in paramHash.Keys)
				{
					object item = paramHash[key];
					if ( key.ToLower()=="sysno" )
						continue;

					if ( index != 0 )
						sb.Append(",");
					index++;

					
					if (item is int || item is decimal)
					{
						sb.Append(key).Append("=").Append(item.ToString());
					}
					else if (item is string)
					{
						sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
					}
					else if (item is DateTime)
					{
						if ( (DateTime)item == AppConst.DateTimeNull )
							sb.Append(key).Append(" = null ");
						else
							sb.Append(key).Append(" = cast(").Append( Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
					}
				}
			}

			sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

			return SqlHelper.ExecuteNonQuery(sb.ToString());
		}

		public int DeleteItem(int poPayItemSysNo)
		{
			string sql = "delete from Ent_Finance_POPay_Item where sysno = " + poPayItemSysNo;
			SqlCommand cmd = new SqlCommand(sql);
			return SqlHelper.ExecuteNonQuery(cmd);
		}

	}
}
